import os
from datetime import datetime
from typing import Callable

import openpyxl
import xlrd as xlrd
from openpyxl import Workbook
from xlrd import xldate_as_tuple

from sam import export_dir_path
from sam.util.fileUtil import assert_is_expect_file_type, UNLESS_FILE_HEAD, setup_export_file_path, \
    get_file_name_from_path


def excel_read_single_sheet(file_path, sheet_index=0, is_skip_first_line=True):
    if not assert_is_expect_file_type(file_path, ".xlsx", ".xls"):
        raise RuntimeError(f"文件 {file_path} 不是 有效的 excel 文件")
    # 文件位置
    excel_file = xlrd.open_workbook(file_path)
    sheet = excel_file.sheet_by_index(sheet_index)
    rows = sheet.nrows
    cols = sheet.ncols
    all_content = []
    range_start = 0
    head = sheet.cell_value(0, 0)
    if head in UNLESS_FILE_HEAD:
        range_start = 1
    for i in range(range_start, rows):
        # 跳过第一行
        if is_skip_first_line:
            if i == range_start:
                continue
        row_content = []
        for j in range(cols):
            # 表格的数据类型
            cell_type = sheet.cell(i, j).ctype
            cell = sheet.cell_value(i, j)
            # ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error。
            if cell_type == 0:
                print(f"文件 {file_path} , sheet: {sheet_index} 读取到的内容有空值：第 {i} 行, 第 {j} 列")
            if cell_type == 2 and cell % 1 == 0:  # 如果是整形
                cell = int(cell)
            elif cell_type == 3:
                # 转成datetime对象
                if cell:
                    date = datetime(*xldate_as_tuple(cell, 0))
                    cell = date.strftime('%Y-%m-%d %H:%M:%S')
            elif cell_type == 4:
                cell = True if cell == 1 else False
            elif cell_type == 5:
                raise RuntimeError(f"读取到的内容有错误： {cell}")
            row_content.append(cell)
        all_content.append(row_content)
    return all_content


def excel_write_single_sheet(write_file_path: str
                             , write_item_list: list
                             , column: list = None
                             ):
    if not assert_is_expect_file_type(write_file_path, ".xlsx", ".xls"):
        raise RuntimeError(f"文件 {write_file_path} 不是 有效的 excel 文件")
    workbook = Workbook()
    worksheet = workbook.active
    if column:
        worksheet.append(column)
    for line in write_item_list:
        worksheet.append(line)
    workbook.save(write_file_path)


def excel_write_single_sheet_append(write_file_path: str
                                    , append_item_list: list):
    new_workbook = openpyxl.load_workbook(write_file_path)
    new_worksheet = new_workbook.active
    for append_item_line in append_item_list:
        new_worksheet.append(append_item_line)

    new_workbook.save(write_file_path)


def convert_xls_2_xlsx(original_file_path) -> str:
    if not assert_is_expect_file_type(original_file_path, ".xls"):
        raise RuntimeError(f"文件 {original_file_path} 不是 有效的 xls 文件")
    new_file_path = f"{original_file_path}"
    if os.path.exists(original_file_path):
        content = excel_read_single_sheet(original_file_path)
        excel_write(new_file_path, content)
        if os.path.exists(original_file_path):
            os.remove(original_file_path)
    return new_file_path


def excel_write(write_file_path: str
                , write_item_list: list
                , column: list = None
                , optional: str = "cover"
                , auto_convert_xls_2_xlsx: bool = True):
    if not write_item_list:
        raise RuntimeError(f"文件内容:  {write_item_list} 为空")
    if not assert_is_expect_file_type(write_file_path, ".xlsx"):
        if auto_convert_xls_2_xlsx:
            write_file_path = convert_xls_2_xlsx(write_file_path)
        else:
            raise RuntimeError(f"文件 {write_file_path} 不是 有效的 xlsx 文件, 需要转换成为 xlsx 文件才能操作")
    if "cover" == optional:
        if os.path.exists(write_file_path):
            os.remove(write_file_path)
        excel_write_single_sheet(write_file_path, write_item_list, column=column)
    else:
        if os.path.exists(write_file_path):
            excel_write_single_sheet_append(write_file_path, write_item_list)
        else:
            excel_write_single_sheet(write_file_path, write_item_list, column=column)


def excel_write_multi_sheet_cover(
        sheet_name_content_map: dict
        , write_file_path: str
):
    if sheet_name_content_map:
        wb = openpyxl.Workbook()
        for sheet_name, sheet_content in sheet_name_content_map.items():
            ws = wb.create_sheet(sheet_name)
            for append_line in sheet_content:
                ws.append(append_line)

        sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        wb.remove(sheet)
        wb.save(write_file_path)
    else:
        print("文件内容有问题，不予写入")


def excel_write_quick(file_name: str, file_content: list, optional: str = "cover"):
    export_file_path = setup_export_file_path(export_file_name=file_name, file_type="xlsx")
    excel_write(export_file_path, file_content, optional=optional)


def handle_by_sheet(original_file_path: str, handle_func: Callable[[str, list, dict], object] = None,
                    outer_params: dict = None):
    if not assert_is_expect_file_type(original_file_path, ".xlsx", ".xls"):
        raise RuntimeError(f"文件 {original_file_path} 不是 有效的 excel 文件")
    # 文件位置
    excel_file = xlrd.open_workbook(original_file_path)
    sheets = excel_file.sheets()
    for sheet in sheets:
        rows = sheet.nrows
        cols = sheet.ncols
        sheet_content = []
        range_start = 0
        head = sheet.cell_value(0, 0)
        if head in UNLESS_FILE_HEAD:
            range_start = 1
        for i in range(range_start, rows):
            row_content = []
            for j in range(cols):
                # 表格的数据类型
                cell_type = sheet.cell(i, j).ctype
                cell = sheet.cell_value(i, j)
                if cell_type == 0:
                    print(f"文件 {original_file_path} , sheet: {sheet.name} 读取到的内容有空值: 第 {i} 行, 第 {j} 列")
                if cell_type == 2 and cell % 1 == 0:  # 如果是整形
                    cell = int(cell)
                elif cell_type == 3:
                    # 转成datetime对象
                    if cell:
                        date = datetime(*xldate_as_tuple(cell, 0))
                        cell = date.strftime('%Y-%m-%d %H:%M:%S')
                elif cell_type == 4:
                    cell = True if cell == 1 else False
                elif cell_type == 5:
                    raise RuntimeError(f"读取到的内容有错误： {cell}")
                row_content.append(cell)
            sheet_content.append(row_content)

        if handle_func:
            handle_func(sheet.name, sheet_content, outer_params)


def split_sheet_2_file(original_file_path: str):
    handle_by_sheet(original_file_path, excel_write_quick)


def get_file_all_sheets(file_path: str):
    if not assert_is_expect_file_type(file_path, ".xlsx", ".xls"):
        raise RuntimeError(f"文件 {file_path} 不是 有效的 excel 文件")
    excel_file = xlrd.open_workbook(file_path)
    sheets = excel_file.sheets()
    return [sheet.name for sheet in sheets]


def filter_by_limit_sheet_names_dict(original_file_path: str, limit_sheet_name_dict: dict, new_file_name: str = None):
    if not assert_is_expect_file_type(original_file_path, ".xlsx", ".xls"):
        raise RuntimeError(f"文件 {original_file_path} 不是 有效的 excel 文件")
    # 文件位置
    excel_file = xlrd.open_workbook(original_file_path)
    if not new_file_name:
        old_file_name = get_file_name_from_path(original_file_path)
        new_file_name = f"new_{old_file_name}"

    workbook = Workbook()
    current_sheet_index = 0
    sheets = excel_file.sheets()

    limit_sheet_name_set = set(limit_sheet_name_dict.keys())
    for sheet in sheets:
        sheet_name = sheet.name
        if sheet_name in limit_sheet_name_set:
            workbook.create_sheet(title=limit_sheet_name_dict.get(sheet_name), index=current_sheet_index)
            workbook.active = current_sheet_index
            worksheet = workbook.active
            current_sheet_index += 1
            rows = sheet.nrows
            cols = sheet.ncols
            range_start = 0
            head = sheet.cell_value(0, 0)
            if head in UNLESS_FILE_HEAD:
                range_start = 1
            for i in range(range_start, rows):
                row_content = []
                for j in range(cols):
                    # 表格的数据类型
                    cell_type = sheet.cell(i, j).ctype
                    cell = sheet.cell_value(i, j)
                    if cell_type == 0:
                        print(f"文件 {original_file_path} , sheet: {sheet.name} 读取到的内容有空值：第 {i} 行, 第 {j} 列")
                    if cell_type == 2 and cell % 1 == 0:  # 如果是整形
                        cell = int(cell)
                    elif cell_type == 3:
                        # 转成datetime对象
                        if cell:
                            date = datetime(*xldate_as_tuple(cell, 0))
                            cell = date.strftime('%Y-%m-%d %H:%M:%S')
                    elif cell_type == 4:
                        cell = True if cell == 1 else False
                    elif cell_type == 5:
                        raise RuntimeError(f"读取到的内容有错误： {cell}")
                    row_content.append(cell)
                else:
                    worksheet.append(row_content)

    blank_sheet = workbook.get_sheet_by_name("Sheet")
    workbook.remove_sheet(blank_sheet)
    new_file_path = f"{export_dir_path}{os.sep}{new_file_name}"
    workbook.save(new_file_path)
